"use strict";

var sqlite3 = require('sqlite3').verbose();

var Article = {
    id: String,
    title: String,
    content: String,
    publish: Date,
    imageLink: String,
    link: String,
};

var dbName = 'data/Articles.sqlite3';

var db = new sqlite3.Database(dbName);

var ArticleDAO = function() {};
module.exports = new ArticleDAO();

function closeDb() {
    db.close(function(err) {
        if (err)
            console.log(eventstr + " " + err);
    });
};

ArticleDAO.prototype.createTable = function createTable(callback) {
    db.run("CREATE TABLE IF NOT EXISTS article (id TEXT PRIMARY KEY, title TEXT, content TEXT, publish INTEGER, imageLink TEXT, link TEXT)",
        function(err) {
            callback(err);
        });
};

ArticleDAO.prototype.save = function(article, callback) {
    var sql = "INSERT INTO article select ?, ?, ?, ?, ?, ?  where not exists(select 1 from article where id = ?)";
    //var sql = "INSERT INTO article values(?, ?, ?, ?, ?, ?)";
    var params = [
        article.id, article.title, article.content,
        article.publish, article.imageLink, article.link, article.id
    ];
    var stmt = db.prepare(sql);
    stmt.run(params, function(err) {
        callback(err);
    });
    stmt.finalize();
};

ArticleDAO.prototype.saveAll = function(articles, callback) {
    articles.forEach(function(article, i) {
        ArticleDAO.prototype.save(article, callback);
    });
}

ArticleDAO.prototype.fetch = function(callback) {
    var results = [];
    db.all("SELECT * FROM article order by publish desc limit 10",
        function(err1, rows) {
            if (err1) {
                callback(results, err1);
            } else {
                if (rows.length > 0) {
                    rows.forEach(function(row, i) {
                        results.push(row);
                    });
                } else {
                    var err = "no data";
                }
                callback(results, err);
            };
        }
    );
};

ArticleDAO.prototype.deleteOld = function(date, callback) {
    db.run("delete from article where publish < " + date,
        function(err) {
            callback(err);
        }
    );
}